﻿CREATE PROCEDURE s_PaymentInsert_01
@LoanID int,@NewPayment money, @DatePaid smalldatetime,@WaterFallID tinyint,@StatusID int
AS 
SET NOCOUNT ON
PRINT OBJECT_NAME(@@PROCID)

SELECT 'Params' Params,@LoanID ,@NewPayment , @DatePaid ,@WaterFallID ,@StatusID 

DECLARE @Deleted_ TABLE(ID int,LoanID int,Amount numeric(18,2),DatePaid datetime,WaterFallID int,StatusID int)
DECLARE @Deleted TABLE(ID int,LoanID int,Amount numeric(18,2),DatePaid datetime,WaterFallID int,StatusID int)
DECLARE @RecalcPrinBalParamID int SET @RecalcPrinBalParamID = dbo.fs_StateParamID__RecalcPrinBal()
SET @StatusID = ISNULL(@StatusID,[dbo].[fs_StateID_Start]('t_Payment'))

DECLARE @PrincBal money,@DateNext smalldatetime,@PrevDatePaid smalldatetime

IF dbo.fs_StateParamValue(@StatusID,@RecalcPrinBalParamID) = 1 BEGIN

DECLARE @CI int
EXEC @CI = sp__Context_SecurityOff 1
DELETE t_Payment
OUTPUT deleted.ID,deleted.LoanID,deleted.Amount,deleted.DatePaid,deleted.WaterFallID,deleted.StatusID INTO @Deleted_
FROM t_Payment WHERE LoanID = @LoanID AND DatePaid > @DatePaid
INSERT INTO @Deleted SELECT * FROM @Deleted_ ORDER BY ID
SELECT '@Deleted'D,* FROM @Deleted
EXEC sp__Context_SecurityOff @CI

SELECT TOP 1 @PrincBal = P.PrincipalBalance,@DateNext = P.DateNext,@PrevDatePaid = P.DatePaid
FROM t_Payment P 
WHERE P.LoanID = @LoanID AND dbo.fs_StateParamValue(StatusID,@RecalcPrinBalParamID) = 1
ORDER BY P.DatePaid DESC, P.ID DESC

DECLARE @PaymentNumber numeric(5,2)
SELECT @PaymentNumber = ISNULL(MAX(PaymentNumber),0) FROM t_Payment P WHERE P.LoanID = @LoanID AND dbo.fs_StateParamValue(StatusID,@RecalcPrinBalParamID) = 1
--SELECT TOP 1 I.ID IID,P.*FROM t_Payment P INNER JOIN inserted i ON i.LoanID = P.LoanID AND i.ID < P.ID ORDER BY P.ID DESC

DECLARE @Rate numeric(9,9),@ToLateFee money,@FirstPmtDate datetime,@Payment money,
	@APR numeric(10,10),@DayCountConvention tinyint,@GraceDays int
SELECT @APR = LI.APR/100.00000000,
			 @Rate = LI.APR/LI.PaymentsPerYear/100,
			 @PrincBal = ISNULL(@PrincBal,LI.AmountFinanced),
			 @FirstPmtDate = LI.FirstPaymentDate,
			 @DateNext = ISNULL(@DateNext,LI.FirstPaymentDate),
			 @PrevDatePaid = ISNULL(@PrevDatePaid,LI.ContractDate),
			 @Payment = LI.Payment,
			 @DayCountConvention = LI.DayCountConventionID
FROM v_TruthInLending LI WHERE @LoanID = LI.LoanID

SELECT			 @ToLateFee = LCI.Charge,@GraceDays = ISNULL(SLC.[Late Days],PS.LateFeeGrace)
FROM v_TruthInLending LI
INNER JOIN t_Loan_Borrower L_B ON L_B.LoanID = LI.LoanID AND L_B.Pos = 0
INNER JOIN t_Address A ON L_B.BorrowerID = A.BorrowerID AND A.Pos = 0
LEFT OUTER JOIN t_StateLateCharge SLC ON SLC.State = A.StateCode
INNER JOIN ts_LoanPaymentType LPT ON LI.PaymentTypeID = LPT.ID
CROSS JOIN t_PaymentSetting PS
CROSS APPLY LateChargeInfo(LI.PrincipalBalance,LI.Payment,LI.PaymentsPerYear,@DateNext,@DatePaid,
	ISNULL(SLC.[Late Days],PS.LateFeeGrace),ISNULL(SLC.[Late Chg%],PS.LateFeePerc),ISNULL(SLC.[Late Chg Max],PS.LateFeeMax))LCI
WHERE LI.LoanID = @LoanID

DECLARE @ToInterest money SET @ToInterest = dbo.ACCRINTM(@PrevDatePaid,@DatePaid,@APR,@PrincBal,@DayCountConvention)

--SELECT 'PEB'T,* FROM PaymentEscrowBalance(@LoanID)
DECLARE @OwnedInterest money,@OwnedLateFee money,@OwnedNSF money
SELECT @OwnedInterest = 0,@OwnedLateFee  = 0,@OwnedNSF = 0
SELECT @OwnedInterest = Interest,@OwnedLateFee = LateFee,@OwnedNSF = NSF FROM PaymentEscrowBalance(@LoanID)

SELECT @OwnedInterest OwnedInterest,@OwnedLateFee OwnedLateFee,@OwnedNSF OwnedNSF
SELECT WF.* INTO #WF
FROM WaterFall (@Payment,@NewPayment,@ToInterest,@ToLateFee,0,@OwnedInterest,@OwnedLateFee,@OwnedNSF) WF
SELECT 'WF' T,* FROM #WF

DECLARE @NewDateNext smalldatetime,@ExpectedBalance money,@ExpectedPayment smallint
SELECT @PaymentNumber = @PaymentNumber + @NewPayment/ @Payment

SELECT @NewDateNext = Date FROM PaymentScheduleByLoanID(@LoanID) CROSS JOIN t_PaymentSetting PS
WHERE PaymentNumber = FLOOR(@PaymentNumber+PS.AmountTolerance)

SELECT @ExpectedBalance = RemainingPrinciple, @ExpectedPayment = PaymentNumber
FROM PaymentScheduleByLoanID(@LoanID) WHERE Date <= @DatePaid ORDER BY PaymentNumber

DECLARE @O TABLE(ID int)
INSERT INTO t_Payment(LoanID,Amount,DateScheduled,DatePaid,DateNext,Interest,LateFee,PrincipalBalance,
											PaymentNumber,ExpectedBalance,ExpectedPayment,WaterFallID,DayCountConventionID,StatusID)
OUTPUT inserted.ID INTO @O
SELECT @LoanID,@NewPayment,@DateNext,@DatePaid,@NewDateNext,@ToInterest,@ToLateFee,
			 @PrincBal - WF.PrincipalPart - WF.TranBalance,
			 @PaymentNumber,@ExpectedBalance,@ExpectedPayment,@WaterFallID,@DayCountConvention,@StatusID
FROM #WF WF
SELECT 'P'P,* FROM t_Payment WHERE LoanID = @LoanID-- ID = (SELECT ID FROM @O)

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT O.ID,WF.ToOwnedInterest,WF.ToOwnedLateFee,WF.ToOwnedNSF FROM #WF WF CROSS JOIN @O O

INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
SELECT O.ID,WF.AddEscrowInterest,WF.AddEscrowLateFee,WF.AddEscrowNSF FROM #WF WF CROSS JOIN @O O

SELECT 'P'P,* FROM t_Payment WHERE ID = (SELECT ID FROM @O)
SELECT 'PT'PT,PT.* FROM v_PaymentTransaction PT INNER JOIN @O O ON O.ID = PT.PaymentID

WHILE EXISTS(SELECT * FROM @Deleted) BEGIN
	SELECT TOP (1) @LoanID = LoanID,@NewPayment = Amount,@DatePaid = DatePaid,@WaterFallID = WaterFallID,@StatusID = StatusID FROM @Deleted
	EXEC s_PaymentInsert @LoanID,@NewPayment,@DatePaid,@WaterFallID,@StatusID
	DELETE TOP(1) FROM @Deleted
END

END
ELSE BEGIN
	SELECT TOP 1 @PrincBal = P.PrincipalBalance
	FROM t_Payment P 
	WHERE P.LoanID = @LoanID AND dbo.fs_StateParamValue(StatusID,@RecalcPrinBalParamID) = 1
	ORDER BY P.DatePaid DESC, P.ID DESC

	INSERT INTO t_Payment(LoanID,Amount,DatePaid,DateScheduled,DateNext,StatusID,DayCountConventionID,PrincipalBalance,ExpectedBalance,Interest,PaymentNumber)
	OUTPUT inserted.ID INTO @O
	VALUES(@LoanID,@NewPayment,@DatePaid,@DatePaid,@DatePaid,@StatusID,0,@PrincBal,0,0,0)

	INSERT INTO v_PaymentTransaction#ForInsert(PaymentID,Interest,LateFee,NSF)
	SELECT O.ID,0,0,-25 FROM @O O
END

--SELECT @PrincBal PrincBal,@DateNext DateNext,@DatePaid DatePaid,@PrevDatePaid PrevDatePaid,@ToInterest ToInterest,
--			 @NewDateNext NewDateNext,@ToLateFee LateCharges,@PaymentNumber Pmt#
--BEGIN TRAN
--DECLARE @Date datetime,@LoanID int SELECT @Date = '7/1/2005',@LoanID = 1051216
--EXEC s_PaymentInsert @LoanID,244.73,@Date,1
--ROLLBACK
